
package br.gen.seam.sqlserver.extract.columns;

import br.gen.seam.util.entity.Column;
import br.gen.seam.util.entity.Constraint;
import br.gen.seam.util.entity.Schema;
import br.gen.seam.util.entity.Table;
import br.gen.seam.util.entity.Type;
import br.gen.seam.util.jdbc.AbstractConnection;
import br.gen.seam.util.jdbc.exception.JDBCException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Diego Araujo | diegogomesaraujo@gmail.com
 * @version 1.0
 */
public class ExtractColumns {

    private List<Schema> schemas;

    private AbstractConnection connection;

    public ExtractColumns(List<Schema> schemas) {
        this.schemas = schemas;
        connection = AbstractConnection.getInstance();
    }

    private void selectColumns(Table table) throws JDBCException {
        connection.execute("SELECT " +
                "C.TABLE_SCHEMA, " +
                "C.TABLE_NAME, " +
                "C.COLUMN_NAME, " +
                "C.IS_NULLABLE, " +
                "CASE C.DATA_TYPE " +
                "   WHEN 'varchar' THEN 'String' " +
                "   WHEN 'char' THEN 'String' " +
                "   WHEN 'datetime' THEN 'Date' " +
                "   WHEN 'int' THEN 'Integer' " +
                "   WHEN 'smallint' THEN 'Integer' " +
                "   WHEN 'bigint' THEN 'Long' " +
                "   WHEN 'numeric' THEN 'BigDecimal' " +
                "   WHEN 'decimal' THEN 'BigDecimal' " +
                "END AS DATA_TYPE," +
                "CASE C.DATA_TYPE " +
                "   WHEN 'varchar' THEN C.CHARACTER_MAXIMUM_LENGTH " +
                "   WHEN 'char' THEN C.CHARACTER_MAXIMUM_LENGTH " +
                "   WHEN 'datetime' THEN C.DATETIME_PRECISION " +
                "   WHEN 'int' THEN C.NUMERIC_PRECISION " +
                "   WHEN 'smallint' THEN C.NUMERIC_PRECISION " +
                "   WHEN 'bigint' THEN C.NUMERIC_PRECISION " +
                "   WHEN 'numeric' THEN C.NUMERIC_PRECISION " +
                "   WHEN 'decimal' THEN C.NUMERIC_PRECISION " +
                "END AS LENGTH, " +
                "CASE C.DATA_TYPE " +
                "   WHEN 'numeric' THEN C.NUMERIC_PRECISION_RADIX " +
                "   WHEN 'decimal' THEN C.NUMERIC_PRECISION_RADIX " +
                "END AS PRECISION_LENGTH " +
                "FROM INFORMATION_SCHEMA.COLUMNS C " +
                "WHERE " +
                "   C.TABLE_SCHEMA='"+ table.getSchema().getName() +"' " +
                "   AND C.TABLE_NAME='"+ table.getName() +"' " +
                "ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME");
    }

    private Column configureObjectColumn(ResultSet resultSet, Table table) throws SQLException, JDBCException {
        Column column = new Column();
        column.setTable(table);
        column.setName(resultSet.getString("COLUMN_NAME"));
        column.setLength(resultSet.getInt("LENGTH"));
        column.setPrecision(resultSet.getInt("PRECISION_LENGTH"));
        column.setType(new Type(resultSet.getString("DATA_TYPE")));
        if(resultSet.getString("IS_NULLABLE").equals("YES")) {
            column.setNullable(true);
        } else {
            column.setNullable(false);
        }
        return column;
    }

    private void extractColumns() throws SqlServerExtractColumnsException {
        try {
            connection.openConnection();
            for(int i=0;i<schemas.size();i++) {
                Schema schema = schemas.get(i);
                List<Table> tables = schema.getTables();
                for(int j=0;j<tables.size();j++) {
                    Table table = tables.get(j);
                    selectColumns(table);
                    ResultSet resultSet = connection.getResultSet();
                    List<Column> columns = new ArrayList<Column>();
                    while(resultSet.next()) {
                        columns.add(configureObjectColumn(resultSet, table));
                    }
                    table.setColumns(columns);
                    tables.set(j, table);
                }
                schema.setTables(tables);
                schemas.set(i, schema);
            }
            connection.closeConnection();
        } catch (JDBCException ex) {
            throw new SqlServerExtractColumnsException("Error when extracting columns: "+ ex.getMessage(), ex);
        } catch (SQLException ex) {
            throw new SqlServerExtractColumnsException("Error when extracting columns: "+ ex.getMessage(), ex);
        }
    }

    private List<String[]> extractKeys(Table table) throws JDBCException, SQLException {
        ResultSet resultSet = connection.executeSpecifiedQuery("SELECT DISTINCT " +
                "   KCU.TABLE_SCHEMA, " +
                "   KCU.TABLE_NAME, " +
                "   C.COLUMN_NAME, " +
                "   KCU_2.TABLE_SCHEMA AS SCHEMA_CONSTRAINT, " +
                "   KCU_2.TABLE_NAME AS TABLE_CONSTRAINT, " +
                "   KCU_2.COLUMN_NAME AS COLUMN_CONSTRAINT, " +
                "   TC.CONSTRAINT_TYPE " +
                "FROM INFORMATION_SCHEMA.COLUMNS C " +
                "   LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU " +
                "       ON C.COLUMN_NAME=KCU.COLUMN_NAME " +
                "   LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC " +
                "       ON TC.TABLE_NAME=KCU.TABLE_NAME AND TC.CONSTRAINT_NAME=KCU.CONSTRAINT_NAME " +
                "   LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC " +
                "       ON RC.CONSTRAINT_NAME=KCU.CONSTRAINT_NAME " +
                "   LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_2 " +
                "       ON KCU_2.CONSTRAINT_NAME=RC.UNIQUE_CONSTRAINT_NAME " +
                "WHERE " +
                "   TC.TABLE_SCHEMA = '"+ table.getSchema().getName() +"' " +
                "   AND TC.TABLE_NAME = '"+ table.getName() +"' " +
                "ORDER BY KCU.TABLE_SCHEMA, KCU.TABLE_NAME, C.COLUMN_NAME");
        List<String[]> keys = new ArrayList<String[]>();
        while(resultSet.next()) {
            String[] infos = new String[7];
            infos[0] = resultSet.getString("TABLE_SCHEMA");
            infos[1] = resultSet.getString("TABLE_NAME");
            infos[2] = resultSet.getString("COLUMN_NAME");
            infos[3] = resultSet.getString("SCHEMA_CONSTRAINT");
            infos[4] = resultSet.getString("TABLE_CONSTRAINT");
            infos[5] = resultSet.getString("COLUMN_CONSTRAINT");
            infos[6] = resultSet.getString("CONSTRAINT_TYPE");
            keys.add(infos);
        }
        return keys;
    }

    private Constraint verifyConstraint(Column column, List<String[]> keys) throws JDBCException, SQLException {
        Constraint constraint = null;
        for(String[] key : keys) {
            if(!(column.getTable().getSchema().getName().equals(key[0]) &&
                    column.getTable().getName().equals(key[1]) &&
                    column.getName().equals(key[2])))
                continue;
            if(key[6].equals("FOREIGN KEY")) {
                Schema schema = new Schema();
                schema.setName(key[3]);
                Table table = new Table();
                table.setName(key[4]);
                table.setSchema(schema);
                Column columnConstraint = new Column();
                columnConstraint.setName(key[5]);
                columnConstraint.setTable(table);
                if(constraint != null && constraint.equals(Constraint.PRIMARY_KEY)) {
                    Column columnTmp = constraint.getColumn();
                    constraint = Constraint.PK_FK_KEY;
                    constraint.setColumn(columnTmp);
                } else {
                    constraint = Constraint.FOREING_KEY;
                    constraint.setColumn(columnConstraint);
                }
            } else if(key[6].equals("PRIMARY KEY")) {
                constraint = Constraint.PRIMARY_KEY;
            } else if(key[6].equals("UNIQUE")) {
                constraint = Constraint.UNIQUE;
            } else {
                if(constraint != null && constraint.equals(Constraint.FOREING_KEY)) {
                    if(key[6].equals("PRIMARY KEY")) {
                        Column columnTmp = constraint.getColumn();
                        constraint = Constraint.PK_FK_KEY;
                        constraint.setColumn(columnTmp);
                    }
                } else {
                    if(key[6].equals("PRIMARY KEY")) {
                        constraint = Constraint.PRIMARY_KEY;
                    }
                }
            }
        }
        return constraint;
    }

    public void addConstraints() throws SqlServerExtractColumnsException {
        try {
            connection.openConnection();
            for(int i=0;i<schemas.size();i++) {
                Schema schema = schemas.get(i);
                List<Table> tables = schema.getTables();
                for(int j=0;j<tables.size();j++) {
                    Table table = tables.get(j);
                    List<Column> columns = table.getColumns();
                    List<String[]> keys = extractKeys(table);
                    for(int c=0;c<columns.size();c++) {
                        Column column = columns.get(c);
                        column.setConstraint(verifyConstraint(column, keys));
                        columns.set(c, column);
                    }
                    table.setColumns(columns);
                    tables.set(j, table);
                }
                schema.setTables(tables);
                schemas.set(i, schema);
            }
            connection.closeConnection();
        } catch (JDBCException ex) {
            throw new SqlServerExtractColumnsException("Error when extracting columns: "+ ex.getMessage(), ex);
        } catch (SQLException ex) {
            throw new SqlServerExtractColumnsException("Error when extracting columns: "+ ex.getMessage(), ex);
        }
    }

    public List<Schema> getSchemas() throws SqlServerExtractColumnsException {
        extractColumns();
        addConstraints();
        return schemas;
    }
    
}
